The present invention relates generally to computer databases, and more particularly, to software that automatically creates a database structure.
Computers are essential to the daily operations of both commercial and non-commercial organizations. In particular, computers are often used to store and process data. In order for a computer to store and process data, the data must be organized in a computer-readable format. Any type of infrastructure and logical organization for storing data is generally called a database. Some examples of databases include spreadsheets, relational databases, and text files.
A database may be used to store data about a context such as a business, educational institution, or government agency. Each context comprises a number of logically related entities that define the context. For example, a leasing company may be a context called LEASING_CO that comprises several related entities, such as customer and account. The relationship between these two entities may be such that each customer can have more than one account. Entities may be expressed in terms of attributes that represent components of the entity. Using the example above, the customer entity may be represented by attributes such as customer birth date (CUST_BIRTH_DATE), customer geographic state (CUST_GEO_STATE), customer number (CUST_NUM), etc.
Known application software tools that allow users to create databases require the user to specify all the tables that are to be created for the database. Specifically, the user inputs the names of the tables and the attributes of those tables. The attributes may be entered as a combination of name, type, length, and sometimes a default value. The software tool then processes the input information and outputs a database structure. According to this method, two separate organizations may have completely different database structures for storing the same type of information. For example, Leasing Company A may decide to represent an account entity in one table that includes all of the entity""s attributes such as the account number (ACCOUNT_NO), the date the account was opened (ACCOUNT_OPEN_DATE), the account type (ACCOUNT_TYPE), the account owner (ACCOUNT_OWNER), and the current month""s payment (PAYMENT_THIS_MONTH). On the other hand, Leasing Company B may decide to distribute the attributes of the account entity into two tables. The first table may include the account number (ACCOUNT_NO), the account type (ACCOUNT_TYPE), and the account owner (ACCOUNT_OWNER) and the second table may include the account number (ACCOUNT_NUMBER), the date the account was opened (ACCOUNT_OPEN_DATE), and payment this month (PAYMENT_THIS_MONTH).
Recently, it has become increasingly important to transfer, exchange, and merge information in databases from a number of different sources. The lack of a common database structure among organizations, such as the two leasing companies described above, makes such information exchange difficult and complex. Further, known database design systems are so complex that only experienced software professionals are capable of understanding them. As a result, the valuable usage of databases largely depends on the personnel maintaining the database system.
Accordingly, there is a need to have a common database design that allows different organizations to easily exchange and merge their databases. Further, there is a need for a database design that is easy to understand and maintain.
The present invention fulfills the need discussed above by providing software that does not require the user to specify the tables created to represent an entity. Rather, the software of the present invention automatically creates three types of tables to represent an entity in a database. The user need only specify the entity represented, the attributes defining that entity, and the category of each attribute. Each attribute is categorized into one of the following categories: (1) identifying; (2) static; (3) semi-dynamic; and (4) dynamic. A static attribute is one whose value remains constant throughout the life span of the instance of the entity. A semi-dynamic attribute is an attribute whose value may change over the life span of the entity but is not recorded on a periodic basis. A dynamic attribute is one whose value is recorded and stored at fixed time intervals for a period of time during the life span of the entity. The number of times the value is recorded within this period of time is called the record frequency.
The software processes the information input by the user and generates the following three types of tables to represent each entity: (1) a static table containing all the static attributes plus the identifying attribute; (2) a semi-dynamic table containing all the semi-dynamic attributes and the identifying attribute; and (3) a set of dynamic tables for every group of dynamic attributes that have the same record frequency, each of the tables containing the dynamic attributes and the identifying attribute.
In a preferred embodiment, the tables of the database structure store information regarding multiple entities and express a relationship between the entities. Specifically, an identifying attribute in a set of tables representing a first entity may be designated as a primary key for that particular entity. In order to express a relationship with a second entity, the primary key of the first entity may be designated as a foreign key in the second entity. The foreign key is preferably stored in the static table of the second entity.
In a second embodiment, the software of the present invention generates reports on the data stored in the database without the user having to write a complicated query. Rather, the user simply selects an attribute and the software generates a report including meaningful information on the values stored in the database for the selected attribute.